import os
import pyodbc
import base64
from flask import Flask, render_template, request, redirect, url_for
from openpyxl import load_workbook
import qrcode
from io import BytesIO
from datetime import datetime
import sys


# 资源路径处理函数
def resource_path(relative_path):
    """获取资源的绝对路径，兼容开发环境和打包环境"""
    try:
        # PyInstaller 创建的临时文件夹
        base_path = sys._MEIPASS
    except AttributeError:
        base_path = os.path.abspath(".")

    return os.path.join(base_path, relative_path)


# 初始化 Flask 应用
app = Flask(__name__)

# 设置动态模板和静态文件路径
app.template_folder = resource_path('templates')
app.static_folder = resource_path('static')

# 修复：正确设置上传文件夹
UPLOAD_FOLDER = os.path.join(os.getcwd(), 'uploads')
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER  # 添加到应用配置

# 确保上传目录存在
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)

# 数据库路径设置
app.config['DATABASE'] = r'\\Jmmd-m70pez39\E\PY_DM\顺引\DATABASE.accdb'


# 添加自定义过滤器
@app.template_filter('b64encode')
def b64encode_filter(data):
    return base64.b64encode(data).decode('utf-8')


# 创建数据库连接
def get_db_connection():
    # 使用绝对路径确保打包后能正确找到数据库
    db_path = os.path.abspath(app.config["DATABASE"])
    print(f"数据库路径: {db_path}")  # 调试信息
    conn_str = (
        r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
        f'DBQ={db_path};'
    )
    return pyodbc.connect(conn_str)


# 初始化数据库
def init_db():
    # 如果数据库文件不存在则创建
    if not os.path.exists(app.config['DATABASE']):
        print(f"数据库文件不存在，尝试创建: {app.config['DATABASE']}")
        try:
            conn = get_db_connection()
            cursor = conn.cursor()
            # 创建主表
            cursor.execute('''CREATE TABLE VehicleDetails (
                                id AUTOINCREMENT PRIMARY KEY,
                                总装连号 INTEGER,
                                项目 TEXT,
                                物料编码 TEXT,
                                物料名称 TEXT,
                                该车采购数量 INTEGER,
                                车身号 TEXT,
                                总装上线日期 DATETIME,
                                物料需求时间 DATETIME,
                                排序 INTEGER,
                                工装车 INTEGER,
                                单号 TEXT
                            )''')
            # 创建历史记录表
            cursor.execute('''CREATE TABLE VehicleDetails2 (
                                id AUTOINCREMENT PRIMARY KEY,
                                总装连号 INTEGER,
                                项目 TEXT,
                                物料编码 TEXT,
                                物料名称 TEXT,
                                该车采购数量 INTEGER,
                                车身号 TEXT,
                                总装上线日期 DATETIME,
                                物料需求时间 DATETIME,
                                排序 INTEGER,
                                工装车 INTEGER,
                                单号 TEXT,
                                导入时间 DATETIME DEFAULT NOW()
                            )''')
            conn.commit()
            conn.close()
            print("数据库创建成功")
        except Exception as e:
            print(f"创建数据库时出错: {e}")
    else:
        print(f"数据库文件已存在: {app.config['DATABASE']}")


# 处理Excel数据并导入数据库
def process_excel(file_path):
    wb = load_workbook(file_path)
    ws = wb.active
    upload_time = datetime.now().strftime("%Y%m%d%H%M%S")
    import_time = datetime.now()  # 记录导入时间

    # 处理合并单元格
    merged_cells = {}
    for merged_range in ws.merged_cells.ranges:
        min_row, min_col, max_row, max_col = merged_range.min_row, merged_range.min_col, merged_range.max_row, merged_range.max_col
        value = ws.cell(row=min_row, column=min_col).value
        for row in range(min_row, max_row + 1):
            for col in range(min_col, max_col + 1):
                merged_cells[(row, col)] = value

    headers = [cell.value for cell in ws[1]]
    conn = get_db_connection()
    cursor = conn.cursor()

    # 清空主表（保留历史表）
    cursor.execute("DELETE FROM VehicleDetails")

    # 修复：为每个工装车创建统一的单号
    cart_date_map = {}  # 存储工装车对应的日期
    cart_number_map = {}  # 工装车到单号的映射

    # 第一遍：收集每个工装车的最早日期
    for row in ws.iter_rows(min_row=2, values_only=False):
        row_data = {}
        for idx, cell in enumerate(row):
            header = headers[idx]
            value = merged_cells.get((cell.row, cell.column), cell.value)
            row_data[header] = value

        cart_num = row_data.get('工装车')
        if cart_num:
            online_date = row_data['总装上线日期']
            if cart_num not in cart_date_map:
                cart_date_map[cart_num] = online_date
            else:
                # 取最早日期
                if isinstance(online_date, datetime) and online_date < cart_date_map[cart_num]:
                    cart_date_map[cart_num] = online_date

    # 第二遍：生成统一单号
    for cart_num, online_date in cart_date_map.items():
        if isinstance(online_date, datetime):
            date_str = online_date.strftime('%Y%m%d')
        else:
            date_str = str(online_date).split()[0].replace('-', '')
        cart_number_map[cart_num] = f"{date_str}{cart_num}{upload_time}"

    # 第三遍：插入数据
    for row in ws.iter_rows(min_row=2, values_only=False):
        row_data = {}
        for idx, cell in enumerate(row):
            header = headers[idx]
            value = merged_cells.get((cell.row, cell.column), cell.value)
            row_data[header] = value

        cart_num = row_data.get('工装车')
        danhao = cart_number_map.get(cart_num) if cart_num else None

        # 插入主表
        cursor.execute('''INSERT INTO VehicleDetails (
                            总装连号, 项目, 物料编码, 物料名称, 该车采购数量, 
                            车身号, 总装上线日期, 物料需求时间, 排序, 工装车, 单号
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                       (row_data['总装连号'], row_data['项目'], row_data['物料编码'],
                        row_data['物料名称'], row_data['该车采购数量'], row_data['车身号'],
                        row_data['总装上线日期'], row_data['物料需求时间'], row_data['排序'],
                        cart_num, danhao))

        # 同时插入历史表（VehicleDetails2）
        cursor.execute('''INSERT INTO VehicleDetails2 (
                            总装连号, 项目, 物料编码, 物料名称, 该车采购数量, 
                            车身号, 总装上线日期, 物料需求时间, 排序, 工装车, 单号, 导入时间
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                       (row_data['总装连号'], row_data['项目'], row_data['物料编码'],
                        row_data['物料名称'], row_data['该车采购数量'], row_data['车身号'],
                        row_data['总装上线日期'], row_data['物料需求时间'], row_data['排序'],
                        cart_num, danhao, import_time))

    conn.commit()
    conn.close()
    return cart_number_map


# 生成二维码图片
def generate_qr_code(danhao):
    qr = qrcode.QRCode(
        version=1,
        error_correction=qrcode.constants.ERROR_CORRECT_L,
        box_size=10,
        border=4,
    )
    qr.add_data(danhao)
    qr.make(fit=True)
    img = qr.make_image(fill_color="black", back_color="white")

    img_byte_arr = BytesIO()
    img.save(img_byte_arr, format='PNG')
    img_byte_arr.seek(0)
    return img_byte_arr


@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        if 'file' not in request.files:
            return redirect(request.url)

        file = request.files['file']
        if file.filename == '':
            return redirect(request.url)

        if file:
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
            file.save(file_path)

            # 处理Excel并导入数据库
            cart_number_map = process_excel(file_path)

            # 重定向到打印页面
            return redirect(url_for('print_page'))

    return render_template('index.html')


@app.route('/print')
def print_page():
    conn = get_db_connection()
    cursor = conn.cursor()

    # 按工装车分组查询
    cursor.execute("SELECT DISTINCT 工装车, 单号 FROM VehicleDetails WHERE 工装车 IS NOT NULL ORDER BY 工装车")
    carts = cursor.fetchall()

    cart_details = []
    for cart in carts:
        cart_num, danhao = cart
        # 查询该工装车所有明细
        cursor.execute("SELECT * FROM VehicleDetails WHERE 工装车=? ORDER BY 排序", (cart_num,))
        details = cursor.fetchall()

        # 获取列名
        columns = [column[0] for column in cursor.description]

        # 转换为字典列表
        details_list = []
        for row in details:
            details_list.append(dict(zip(columns, row)))

        # 计算物料编码汇总
        material_summary = {}
        for item in details_list:
            material_code = item['物料编码']
            material_name = item['物料名称']
            quantity = item['该车采购数量'] or 0
            
            if material_code in material_summary:
                material_summary[material_code]['quantity'] += quantity
            else:
                material_summary[material_code] = {
                    'name': material_name,
                    'quantity': quantity
                }

        # 生成二维码
        qr_img = generate_qr_code(danhao)

        cart_details.append({
            'cart_num': cart_num,
            'danhao': danhao,
            'qr_code': qr_img.getvalue(),
            'details': details_list,
            'material_summary': material_summary
        })

    conn.close()
    # 添加当前时间
    now = datetime.now()
    return render_template('print.html', cart_details=cart_details, now=now)


if __name__ == '__main__':
    # 确保上传目录存在
    os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
    # 初始化数据库
    try:
        init_db()
        print("应用程序启动成功")
        app.run(host='0.0.0.0', port=5006)
    except Exception as e:
        print(f"应用程序启动失败: {e}")
        input("按回车键退出...")